# function to retrieve all available pattern for a method and Pversion
getAllStopLogicPatterns <- function (table="orderLogicTest", method="fixedPct1", Pversion=0, group="all") {
  sqlString <- function() {
    sql <- sprintf("SELECT DISTINCT 
                   `group`, `pattern`, `direction`, `duration`
                   FROM `%s`
                   WHERE `method` = '%s' AND 
                   `Pversion` = %d", table, method, Pversion)
    if (group!="all") {
      sql <- paste(sql, sprintf("AND `group` = '%s'", group))
    }
    return (sql)
  }
  countSqlString <- function(df) {
    sql <- sprintf("SELECT COUNT(*) 
                   FROM `%s` 
                   WHERE `method` = '%s'
                   AND `group` = '%s'
                   AND `pattern` = '%s'
                   AND `direction` = '%s'
                   AND `duration` = '%s'
                   AND `Pversion` = %d",
                   table, method, df["group"],
                   df["pattern"], df["direction"], df["duration"],
                   Pversion)
    if (group!="all") {
      sql <- paste(sql, sprintf("AND `group` = '%s'", group))
    }
    return (sql)
  }
  con <- connectToVadb()
  values <- dbGetQuery(con, sqlString())
  values$count <- 0
  for (i in 1:dim(values)[1]) {
    values[i,"count"] <- dbGetQuery(con, countSqlString(values[i,]))
  }
  dbDisconnect(con)
  return(values)
  }

# function to show top strategies in terms of mean above zero
getTopStopLogicPatterns <- function (group, method="fixedPct1", Pversion=0, table="orderLogicTest", 
                                     optimize="ratio", duration=10, ordered=TRUE, latex=FALSE, top=0) {
  if('package:xtable' %in% search() || require('xtable',quietly=TRUE)) {}
  rownames=c("notrades", "LL", "WL", "SLPct", "SWPct", "Mean", "DDev", "PWin", "RRRatio")
  retDF <-data.frame()
  AP <- getAllStopLogicPatterns(method=method, Pversion=Pversion, table=table, group=group)
  
  for (i in 1:dim(AP)[1]) {
    # get best configuration in terms of mean or mean/ddev ratio
    if (optimize=="mean") {
      SD <- stopDiagram(group=group, pattern=AP[i,"pattern"], Pversion=Pversion, method=method, 
                        direction=AP[i,"direction"], duration=duration, table=table, orderby="mean")      
    } else if (optimize=="ratio") {
      SD <- stopDiagram(group=group, pattern=AP[i,"pattern"], Pversion=Pversion, method=method, 
                        direction=AP[i,"direction"], duration=duration, table=table, orderby="meanddev")      
    }
    # add configs with positive mean to topTableDf
    if (SD[1,"returnmean"] > 0 & SD[1,"numberoftrades"] > 10 ) {
      thisDF <- data.frame(c(SD[1,"numberoftrades"], SD[1,"stoplosslimit"], SD[1,"stopwinlimit"],
                             SD[1,"stoplosspct"], SD[1,"stopwinpct"], SD[1,"returnmean"], 
                             SD[1,"returnddev"], SD[1,"pctwin"], (SD[1,"returnmean"]/SD[1,"returnddev"])), 
                           row.names=rownames, check.rows=TRUE, check.names=TRUE)
      colnames(thisDF) <- paste(AP[i,"pattern"], AP[i,"direction"], sep="_")
      
      if (dim(retDF)[1] == 0) {
        retDF <- thisDF
      } else {
        retDF <- cbind(retDF, thisDF)
      }
    }
  }
  # return transposed dataframe
  retDF <- as.data.frame(t(retDF))
  
  if (ordered ==TRUE) {
    if (optimize=="mean") {
      retDF <- retDF[with(retDF, order(-Mean)), ]
      tpart <- "auf maximale Durchschnittsrendite"
    } else if (optimize=="ratio") {
      retDF <- retDF[with(retDF, order(-RRRatio)), ]
      tpart <- "auf bestes Rendite/Risiko-Verhältnis"
    }
    if (top!=0) {
      retDF <- retDF[1:top,]
    }
    if (latex==FALSE) {
      return(retDF)      
    }
  } else {
    if (latex==FALSE) {
      if (top!=0) {
        retDF <- retDF[1:top,]
      }
      return(retDF)      
    }
  }
  # prepare latex table output
  title <- sprintf("Beste %d Kursmuster im Test-Datensatz %s 
                   (Stoplimits optimiert %s)", dim(retDF)[1], group, tpart)
  label <- sprintf("tab:topSFP:%s:%s",group, optimize)
  xt <- xtable(retDF,
               caption=title,
               label=label,
               align="|r|r|rr|cccccc|",
               display=c("s","d",
                         "d","d","f","f","f","f","f","f"))
  print(xt)
}

# echoes all *latex* tables of TopStopLogicPatterns
getAllTopStopLogicPatterns <- function(method="fixedPct1", Pversion=0, table="orderLogicTest", 
                                       duration=10, top=10) {
  getTopStopLogicPatterns("INDEX_MAJORS", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("INDEX_MAJORS", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("DJI_STOCKS", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("DJI_STOCKS", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("SMALL_CAPS", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("SMALL_CAPS", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("METALS", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("METALS", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("AGRICULTURE", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("AGRICULTURE", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("FOREX_MAJORS", optimize="mean", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
  getTopStopLogicPatterns("FOREX_MAJORS", optimize="ratio", Pversion=Pversion, table=table, duration=duration, latex=TRUE, top=top)
}

# plot the diagram
#values <- stopDiagram("AGRICULTURE", "Bull.Engulfing")
#nolzero <- eliminateData(values, "stoplosslimit", 0, keep=FALSE)
#v1 <- eliminateData(nolzero, "stopwinlimit", 0, keep=FALSE)
#v1$swl <- as.character(v1$stopwinlimit)
#p <- ggplot(v1) 
#p + geom_line(aes(x=stoplosslimit, y=returnmean, group=stopwinlimit, colour=swl, size=swl)) +
#  scale_colour_manual(values=c("0"="red", "4"="#0000FF", "8"="#0000D6", "12"="red", "16"="blue", "20"="green")) + 
#  scale_fill_manual(values=c("0"="red", "4"="#0000FF", "8"="#0000D6", "12"="red", "16"="blue", "20"="green")
